Partial Class ContextoBAseDataContext

    Public Function buscarUsuarioPorFiltro(ByVal pCampo As String, ByVal pCondicion As String, ByVal pTexto As String) As IEnumerable(Of usuarios_personas)

        Dim sSql As String
        'Dim LISTA As List(Of clsPersonas)
        Dim LISTA As List(Of usuarios_personas)

        sSql = "select per.usuario,us.pass,per.nombre,per.apellido,per.dni,per.tel,per.mail,sec.id_sector,sec.txt_desc "
        sSql = sSql & " from personas per inner join usuarios us on us.usuario=per.usuario "
        sSql = sSql & " inner join sector sec on sec.id_sector=per.id_sector  where "
        If pCampo = "Usuario" Then
            sSql = sSql & "per." & pCampo
        ElseIf pCampo = "Nombre" Then
            sSql = sSql & "per." & pCampo
        ElseIf pCampo = "Apellido" Then
            sSql = sSql & "per." & pCampo
        End If


        If pCondicion = "Mayor que" Then
            sSql = sSql & " > " & pTexto.Trim
        ElseIf pCondicion = "Menor que" Then
            sSql = sSql & " < " & pTexto.Trim
        ElseIf pCondicion = "Igual a" Then
            sSql = sSql & " = " & pTexto.Trim
        ElseIf pCondicion = "Distinto de" Then
            sSql = sSql & " <> " & pTexto
        End If



        If pCondicion = "Comienza con" Then
            sSql = sSql & " like '" & pTexto.Trim & "%'"
        ElseIf pCondicion = "Contiene" Then
            sSql = sSql & " like '%" & pTexto.Trim & "%'"
        ElseIf pCondicion = "Finaliza en" Then
            sSql = sSql & " like '%" & pTexto.Trim & "'"
        ElseIf pCondicion = "Igual que" Then
            sSql = sSql & " like '" & pTexto.Trim & "'"
        End If

        'LISTA = ExecuteQuery(Of clsPersonas)(sSql).ToList


        Return ExecuteQuery(Of usuarios_personas)(sSql).ToList

    End Function

    

    Public Function trae_modulos_usuario(ByVal usuario As String) As IEnumerable(Of usuario_modulos)
        Dim ssql As String
        ssql = "select * from usuario_modulos where usuario='" & usuario & "'"
        Return ExecuteQuery(Of usuario_modulos)(ssql).ToList
    End Function

    Public Function trae_modulos_x_sector(ByVal id_sector As Int32) As IEnumerable(Of modulos_x_sector)
        Dim ssql As String
        ssql = "select * from modulos_x_sector where id_sector=" & id_sector

        Return ExecuteQuery(Of modulos_x_sector)(ssql).ToList
    End Function

    Public Function trae_submodulos_usuario(ByVal usuario As String) As IEnumerable(Of usuario_submodulos)
        Dim ssql As String
        ssql = "select id_modulo,id_submodulos from usuario_submodulos where usuario='" & usuario & "' order by id_modulo ,id_submodulo"
        Return ExecuteQuery(Of usuario_submodulos)(ssql).ToList
    End Function

    Public Function trae_sector_usuario(ByVal usuario As String) As IEnumerable(Of sector)
        Dim ssql As String
        ssql = "select us.id_sector from personas us inner join sector sec on us.id_sector=sec.id_sector  where usuario='" & usuario & "' and sec.estado=1"
        Return ExecuteQuery(Of sector)(ssql).ToList
    End Function

    Public Function trae_sector(ByVal usuario As String) As IEnumerable(Of sector)
        Dim ssql As String
        ssql = "select * from sector where usuario='" & usuario & "' and sec.estado=1"
        Return ExecuteQuery(Of sector)(ssql).ToList
    End Function

    Public Function traer_sectores() As List(Of sector)
        Dim lista As List(Of sector)
        lista = sp_trae_sectores(1).ToList
        Return lista
    End Function


    Public Function Borrar_usuario(ByVal usuario As String) As Boolean
        Dim bol As Boolean
        Dim db As New ContextoBAseDataContext

        Try
            bol = False
            db.sp_borrar_usuario(usuario)
            bol = True
        Catch ex As Exception
            Throw New Exception(ex.Message)
            bol = False
        End Try

    End Function
    
End Class

